<?php
/**
 * Created by JetBrains PhpStorm.
 * User: Administrator
 * Date: 13-5-3
 * Time: 上午11:06
 * To change this template use File | Settings | File Templates.
 */

// +----------------------------------------------------------------------
// | quanli team
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2020 http://www.fistforward.com/ All rights reserved.
// +----------------------------------------------------------------------
// | create by allen.qiang
// +----------------------------------------------------------------------

/**
 * 统计操作类 marketingHandler
 * @package  htdocs
 * @subpackage  quanliadmin
 * create by allen.qiang
 * @version 1.0
 * =================================================================
 * @param $dbca                          DBCA
 * =================================================================
 * @abstract searchPermission()          查询权限信息
 * @abstract editPermission()            编辑、新增权限
 * @abstract getPermissionGroup()        获得所有的权限组
 * @abstract deletePermission()          删除权限信息
 * @abstract searchRoleGroup()           查询角色组信息
 * @abstract editRoleGroup()             编辑、新增角色组
 * @abstract searchRole()                查询角色信息
 * @abstract editRole()                  编辑、新增角色
 * @abstract searchRolePermission()      查询全部角色权限信息
 * @abstract editRolePermission()        分配角色权限信息
 * @abstract searchAllUser()             查询全部用户信息
 * @abstract getRoleAll()                获得全部角色信息并组装成二维数组
 * @abstract editUserRole()              新增、修改用户（含编辑用户角色）
 * @abstract changeUseFlg()              用户状态修改
 * @abstract deleteUser()                删除用户
 * =================================================================
 */


class chartsHandler {
	private $dbca;
	
	public function __construct( $dbca ) {
		$this->dbca = $dbca;
	}
	
	/* 
	 * 查询活动参与信息（针对活动进行统计）
	 *  */
	public function searchProjectInfo ($importData=array()){
		//返回数组
		$return_data = array();
			
		//错误信息
		$error = '';
		
		/* 传入数据有效性验证start */
		if (isset($importData['groupID']) && !empty($importData['groupID']) && !is_numeric($importData['groupID'])){
			$error .= '活动分类必须为数字<br/>';
		}
		
		if (isset($importData['startDate']) && !is_string($importData['startDate'])){
			$error .= '活动时间开始时间必须为字符串<br/>';
		}
		
		if (isset($importData['endDate']) && !is_string($importData['endDate'])){
			$error .= '活动时间结束时间必须为字符串<br/>';
		}
		
		if (!empty($importData['startDate']) && !empty($importData['endDate']) && $importData['endDate'] < $importData['startDate']){
			$error .= '开始时间不能大于结束时间<br/>';
		}
		
		if (isset($importData['projectID']) && !empty($importData['projectID']) && !is_numeric($importData['projectID'])){
			$error .= '活动ID必须为数字<br/>';
		}
		
		if (isset($importData['page']) && !empty($importData['page']) && !is_numeric($importData['page'])){
			$error .= '当前页码必须为数字<br/>';
		}
		
		if (isset($importData['rp']) && !empty($importData['rp']) && !is_numeric($importData['rp'])){
			$error .= '每页记录数必须为数字<br/>';
		}
		
		if (!empty($error)){
			$return_data['code'] = 400;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = $error;
			return $return_data;
		}
		/* 传入数据有效性验证end */
		
		$startDate = isset($importData['startDate']) ? trim($importData['startDate']) : '';//活动名
		$endDate = isset($importData['endDate']) ? trim($importData['endDate']) : '';//活动名
		$projectID = isset($importData['projectID']) ? trim($importData['projectID']) : '';//活动ID
		$groupID = isset($importData['groupID']) ? trim($importData['groupID']) : '';//分类ID
		$page = isset($importData['page']) ? trim($importData['page']) : '';//当前页
		$rp = isset($importData['rp']) ? trim($importData['rp']) : '';//每页记录数
		$useFlg = isset($importData['useFlg']) ? trim($importData['useFlg']) : null;//启用状态
		
		//求总记录数
		$sql_count = 'SELECT COUNT(*) countNum FROM project_detail pd
						LEFT JOIN project_analyse_times pat ON pd.projectID = pat.projectID';
		
		//查询SQL语句
		$sql_search = 'SELECT pat.createTime, pd.projectID, pd.projectName, pat.participateTime, pat.getGiftTime FROM project_detail pd 
						LEFT JOIN project_analyse_times pat ON pd.projectID = pat.projectID';
		
		//排序
		$str_order = ' ORDER BY pat.createTime DESC';
		
		//分页
		$str_limit = '';
		
		//查询条件
		$str_search = ' WHERE';
		
		//查询参数类型
		$type_search = '';
		
		//查询参数数组
		$param_search = array();
		
		//查询条件：活动ID
		if (!empty($projectID)){
			$str_search .= ' AND pd.projectID = ?';
			$type_search .= 'i';
			$param_search[] = $projectID;
		}
		
		//查询条件：活动分类ID
		if (!empty($groupID)){
			$str_search .= ' AND pd.groupID = ?';
			$type_search .= 'i';
			$param_search[] = $groupID;
		}
		
		//查询条件：活动时间
		if (!empty($startDate)){
			$str_search .= ' AND pat.createTime >= ?';
			$type_search .= 's';
			$param_search[] = $startDate;
		}
		
		//查询条件：活动时间
		if (!empty($endDate)){
			$str_search .= ' AND pat.createTime <= ?';
			$type_search .= 's';
			$param_search[] = $endDate;
		}
		
		//查询条件：启用状态
		if (!is_null($useFlg)){
			$str_search .= ' AND pd.useFlg = ?';
			$type_search .= 'i';
			$param_search[] = $useFlg;
		}
			
		//分页
		if (!empty($page) && !empty($rp)){
			$str_limit = sprintf(' LIMIT %d, %d', ($page - 1) * $rp, $rp);
		}
				
		//组装SQL语句
		if (!empty($type_search)){
			$str_search = str_replace('WHERE AND', 'WHERE', $str_search);
			$sql_count = $sql_count.$str_search;
			$sql_search = $sql_search.$str_search.$str_order.$str_limit;
		}else {
			$sql_search = $sql_search.$str_order.$str_limit;
		}
		
		$value_count = empty($type_search) ? $this->dbca->stmt_query($sql_count) : $this->dbca->stmt_query($sql_count,$type_search,$param_search);
		
		if ($value_count){
		
			$return_data['code'] = 200;
		
			//总记录条数
			$return_data['message']['total'] = $value_count[0]->countNum;
		
			//得到查询结果
			$return_data['message']['data'] = empty($type_search) ? $this->dbca->stmt_query($sql_search) : $this->dbca->stmt_query($sql_search,$type_search,$param_search);
		
		}else {
			$return_data['code'] = 200;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = '';
		}
		
		return $return_data;
	}
	
	/* 
	 * 查询参与并获得礼品的会员列表
	 *  */
	public function searchProjectCustList ($importData=array()){
		//返回数组
		$return_data = array();
			
		//错误信息
		$error = '';
		
		/* 传入数据有效性验证start */
		if (isset($importData['createTime']) && !empty($importData['createTime']) && !is_string($importData['createTime'])){
			$error .= '时间必须为字符串<br/>';
		}
		
		if (isset($importData['projectID']) && !empty($importData['projectID']) && !is_numeric($importData['projectID'])){
			$error .= '活动ID必须为数字<br/>';
		}
		
		if (isset($importData['page']) && !empty($importData['page']) && !is_numeric($importData['page'])){
			$error .= '当前页码必须为数字<br/>';
		}
		
		if (isset($importData['rp']) && !empty($importData['rp']) && !is_numeric($importData['rp'])){
			$error .= '每页记录数必须为数字<br/>';
		}
		
		if (!is_null($importData['succedFlg']) && !in_array($importData['succedFlg'], array(0,1))){
			$error .= '是否领取必须为数字<br/>';
		}
		
		if (!empty($error)){
			$return_data['code'] = 400;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = $error;
			return $return_data;
		}
		/* 传入数据有效性验证end */
		
		$createTime = isset($importData['createTime']) ? trim($importData['createTime']) : '';//参与时间
		$projectID = isset($importData['projectID']) ? trim($importData['projectID']) : '';//活动ID
		$page = isset($importData['page']) ? trim($importData['page']) : '';//当前页
		$rp = isset($importData['rp']) ? trim($importData['rp']) : '';//每页记录数
		$succedFlg = in_array($importData['succedFlg'], array(0,1)) ? trim($importData['succedFlg']) : '';//启用状态
		
		//求总记录数
		$sql_count = 'SELECT COUNT(*) countNum FROM project_customer pc';
		
		//查询SQL语句
		$sql_search = 'SELECT cm.custID, cd.custName, pc.ruleName, pc.goodsID, pc.goodsName, pc.succedFlg, cd.provinceName,
						cd.cityName, cd.countyName, cd.address, cd.postalCode, cd.phone, cd.mobile, cm.email, cm.pcustID, pc.createTime
						FROM project_customer pc 
						INNER JOIN cust_master cm ON pc.pcustID = cm.pcustID
						LEFT JOIN cust_deliver cd ON pc.pcustID = cd.pcustID AND pc.deliverID = cd.deliverID';
		
		//分页
		$str_limit = '';
		
		//查询条件
		$str_search = ' WHERE';
		
		//查询参数类型
		$type_search = '';
		
		//查询参数数组
		$param_search = array();
		
		//查询条件：活动ID
		if (!empty($projectID)){
			$str_search .= ' AND pc.projectID = ?';
			$type_search .= 'i';
			$param_search[] = $projectID;
		}
		
		//查询条件：活动时间
		if (!empty($createTime)){
			$str_search .= ' AND pc.createTime BETWEEN ? AND ?';
			$type_search .= 'ss';
			$param_search[] = $createTime.' 00:00:00';
			$param_search[] = $createTime.' 23:59:59';
		}
		
		//查询条件：启用状态
		if ($succedFlg != ''){
			$str_search .= ' AND pc.succedFlg = ?';
			$type_search .= 'i';
			$param_search[] = $succedFlg;
		}
			
		//分页
		if (!empty($page) && !empty($rp)){
			$str_limit = sprintf(' LIMIT %d, %d', ($page - 1) * $rp, $rp);
		}
		
		//组装SQL语句
		if (!empty($type_search)){
			$str_search = str_replace('WHERE AND', 'WHERE', $str_search);
			$sql_count = $sql_count.$str_search;
			$sql_search = $sql_search.$str_search.$str_limit;
		}else {
			$sql_search = $sql_search.$str_limit;
		}
// 		error_log(print_r($sql_search,true));
		$value_count = empty($type_search) ? $this->dbca->stmt_query($sql_count) : $this->dbca->stmt_query($sql_count,$type_search,$param_search);
		
		if ($value_count){
		
			$return_data['code'] = 200;
		
			//总记录条数
			$return_data['message']['total'] = $value_count[0]->countNum;
		
			//得到查询结果
			$return_data['message']['data'] = empty($type_search) ? $this->dbca->stmt_query($sql_search) : $this->dbca->stmt_query($sql_search,$type_search,$param_search);
		
		}else {
			$return_data['code'] = 200;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = '';
		}
		
		return $return_data;
	}
	
	/* 
	 * 查询关注度列表
	 *  */
	public function searchFocusList ($importData=array()){
		$return_data = array();
			
		$error_msg = '';
			
		/* 传入数据有效性验证start */
		if (isset($importData['page']) && !empty($importData['page']) && !is_numeric($importData['page'])){
			$error_msg .= '当前页码必须为数字<br/>';
		}
			
		if (isset($importData['rp']) && !empty($importData['rp']) && !is_numeric($importData['rp'])){
			$error_msg .= '每页记录数必须为数字<br/>';
		}
			
		if (isset($importData['sortname']) && !empty($importData['sortname']) && !is_string($importData['sortname'])){
			$error_msg .= '排序方式必须为字符串<br/>';
		}
			
		if (isset($importData['sortorder']) && !empty($importData['sortorder']) && !is_string($importData['sortorder'])){
			$error_msg .= '排序方式必须为字符串<br/>';
		}
			
		if (isset($importData['startDate']) && !empty($importData['startDate']) && !is_string($importData['startDate'])){
			$error_msg .= '开始时间必须为字符串<br/>';
		}
		
		if (isset($importData['endDate']) && !empty($importData['endDate']) && !is_string($importData['endDate'])){
			$error_msg .= '结束时间必须为字符串<br/>';
		}
		
		if (!empty($error_msg)){
		
			$return_data['code'] = 400;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = $error_msg;
			return $return_data;
		}
		/* 传入数据有效性验证end */
			
		$page = isset($importData['page']) ? trim($importData['page']) : null;//当前页
		$rp = isset($importData['rp']) ? trim($importData['rp']) : null;//每页记录数
		$sortname = isset($importData['sortname']) ? trim($importData['sortname']) : null;//排序字段名
		$sortorder = isset($importData['sortorder']) ? trim($importData['sortorder']) : null;//排序方式（asc,desc）
		$startDate = !empty($importData['startDate']) ? trim($importData['startDate']) : null;//开始时间
		$endDate = !empty($importData['endDate']) ? trim($importData['endDate']) : null;//结束时间
		
		//
		$sql_search = 'SELECT DATE_FORMAT(createTime, \'%Y-%m-%d\') focusTime, COUNT(*) focus, 
						SUM(CASE WHEN pcustID IS NOT NULL THEN 1 ELSE 0 END) regist
						FROM wechat_user_record';
		
		//排序
		$str_order = '';
			
		//分页
		$str_limit = '';
			
		//查询条件
		$str_search = ' WHERE';
		
		//分组
		$str_group = ' GROUP BY focusTime';
			
		//查询参数类型
		$type_search = '';
			
		//查询参数数组
		$param_search = array();
		
		//开始时间
		if (!empty($startDate)){
			$str_search .= ' AND createTime >= ?';
			$type_search .= 's';
			$param_search[] = $startDate;
		}
		
		//结束时间
		if (!empty($endDate)){
			$str_search .= ' AND createTime <= ?';
			$type_search .= 's';
			$param_search[] = $endDate;
		}
			
		//分页
		if (!empty($page) && !empty($rp)){
			$str_limit = sprintf(' LIMIT %d, %d', ($page - 1) * $rp, $rp);
		}
			
		//排序
		if (!empty($sortname) && !empty($sortorder)){
			$str_order = sprintf(' ORDER BY %s %s', $sortname, $sortorder);
		}
			
		//组装SQL语句
		if (!empty($type_search)){
			$str_search = str_replace('WHERE AND', 'WHERE', $str_search);
			$sql_search = $sql_search.$str_search.$str_group.$str_order.$str_limit;
		}else {
			$sql_search = $sql_search.$str_group.$str_order.$str_limit;
		}

		$value_search = empty($type_search) ? $this->dbca->stmt_query($sql_search) : $this->dbca->stmt_query($sql_search,$type_search,$param_search);
		
		if (!empty($value_search)){
			$return_data['code'] = 200;
			//总记录条数
			$return_data['message']['total'] = count($value_search);
			//得到查询结果
			$return_data['message']['data'] = $value_search;
		}else {
			$return_data['code'] = 200;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = '';
		}
			
		return $return_data;
	}
	
	/* 
	 * 查询关注会员信息
	 *  */
	public function searchFocusCustDetail ($importData){
		$return_data = array();
			
		$error_msg = '';
			
		/* 传入数据有效性验证start */
		if (isset($importData['page']) && !empty($importData['page']) && !is_numeric($importData['page'])){
			$error_msg .= '当前页码必须为数字<br/>';
		}
			
		if (isset($importData['rp']) && !empty($importData['rp']) && !is_numeric($importData['rp'])){
			$error_msg .= '每页记录数必须为数字<br/>';
		}
			
		if (isset($importData['sortname']) && !empty($importData['sortname']) && !is_string($importData['sortname'])){
			$error_msg .= '排序方式必须为字符串<br/>';
		}
			
		if (isset($importData['sortorder']) && !empty($importData['sortorder']) && !is_string($importData['sortorder'])){
			$error_msg .= '排序方式必须为字符串<br/>';
		}
			
		if (isset($importData['focusTime']) && !empty($importData['focusTime']) && !is_string($importData['focusTime'])){
			$error_msg .= '关注时间必须为字符串<br/>';
		}
		
		if (!empty($error_msg)){
		
			$return_data['code'] = 400;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = $error_msg;
			return $return_data;
		}
		/* 传入数据有效性验证end */
			
		$page = isset($importData['page']) ? trim($importData['page']) : null;//当前页
		$rp = isset($importData['rp']) ? trim($importData['rp']) : null;//每页记录数
		$sortname = isset($importData['sortname']) ? trim($importData['sortname']) : null;//排序字段名
		$sortorder = isset($importData['sortorder']) ? trim($importData['sortorder']) : null;//排序方式（asc,desc）
		$focusTime = !empty($importData['focusTime']) ? trim($importData['focusTime']) : null;//关注时间
		
		//求总数
		$sql_count = 'SELECT COUNT(*) countNum FROM cust_master cm INNER JOIN wechat_user_record wur ON cm.pcustID = wur.pcustID';
		
		//查询
		$sql_search = 'SELECT cm.pcustID, cm.custID, cm.mobile, cm.sexy, wur.province, wur.city 
						FROM cust_master cm INNER JOIN wechat_user_record wur ON cm.pcustID = wur.pcustID';
		
		//排序
		$str_order = '';
			
		//分页
		$str_limit = '';
			
		//查询条件
		$str_search = ' WHERE';
		
		//查询参数类型
		$type_search = '';
			
		//查询参数数组
		$param_search = array();
		
		//开始时间
		if (!empty($focusTime)){
			$str_search .= ' AND wur.createTime BETWEEN ? AND ?';
			$type_search .= 'ss';
			$param_search[] = $focusTime.' 00:00:00';
			$param_search[] = $focusTime.' 23:59:59';
		}
			
		//分页
		if (!empty($page) && !empty($rp)){
			$str_limit = sprintf(' LIMIT %d, %d', ($page - 1) * $rp, $rp);
		}
			
		//排序
		if (!empty($sortname) && !empty($sortorder)){
			$str_order = sprintf(' ORDER BY %s %s', $sortname, $sortorder);
		}
			
		//组装SQL语句
		if (!empty($type_search)){
			$str_search = str_replace('WHERE AND', 'WHERE', $str_search);
			$sql_count = $sql_count.$str_search;
			$sql_search = $sql_search.$str_search.$str_order.$str_limit;
		}else {
			$sql_search = $sql_search.$str_order.$str_limit;
		}
		
		$value_count = empty($type_search) ? $this->dbca->stmt_query($sql_count) : $this->dbca->stmt_query($sql_count,$type_search,$param_search);
		
		if ($value_count){
		
			$return_data['code'] = 200;
		
			//总记录条数
			$return_data['message']['total'] = $value_count[0]->countNum;
		
			//得到查询结果
			$return_data['message']['data'] = empty($type_search) ? $this->dbca->stmt_query($sql_search) : $this->dbca->stmt_query($sql_search,$type_search,$param_search);
		
		}else {
			$return_data['code'] = 200;
			$return_data['message']['total'] = 0;
			$return_data['message']['data'] = '';
		}
			
		return $return_data;
	}
	
	/* 
	 * 用户活跃度分析
	 *  */
	public function getLiveAnalyse ($importData){
		$return_data = array();
			
		$error_msg = '';
		
		if (!isset($importData['lastDay_less']) || empty($importData['lastDay_less'])){
			$error_msg .= '开始时间节点不能为空<br/>';
		}
		
		if (!isset($importData['lastDay_more']) || empty($importData['lastDay_more'])){
			$error_msg .= '结束时间节点不能为空<br/>';
		}
		
		if ($importData['lastDay_less'] < $importData['lastDay_more']){
			$error_msg .= '开始时间不能大于结束时间<br/>';
		}
		
		if (!isset($importData['lastTimes_less']) || empty($importData['lastTimes_less'])){
			$error_msg .= '消息次数开始节点不能为空<br/>';
		}
		
		if (!isset($importData['lastTimes_more']) || empty($importData['lastTimes_more'])){
			$error_msg .= '消息次数结束节点不能为空<br/>';
		}
		
		if ($importData['lastTimes_less'] > $importData['lastTimes_more']){
			$error_msg .= '开始次数节点不能大于结束次数节点<br/>';
		}
		
		if (!empty($error_msg)){
			return array('code'=>400, 'message'=>$error_msg);
		}
		
		$lastDay_less = trim($importData['lastDay_less']);
		$lastDay_more = trim($importData['lastDay_more']);
		$lastTimes_less = trim($importData['lastTimes_less']);
		$lastTimes_more = trim($importData['lastTimes_more']);
		
		//所有微信注册会员
		$sql_count = 'SELECT COUNT(*) countNum FROM wechat_user_record WHERE pcustID IS NOT NULL';
		
		//lastTime最后发送消息时间；messageTimes发送消息总次数
		$sql_analyse = 'SELECT MAX(wml.createTime) lastTime, COUNT(*) messageTimes
						FROM wechat_message_log wml 
						INNER JOIN wechat_user_record wur ON wml.openId = wur.openId 
						WHERE wur.pcustID IS NOT NULL AND wml.type = 2 GROUP BY wur.pcustID';
		
		$result_count = $this->dbca->stmt_query($sql_count);
		
		if (empty($result_count)){
			$return_data = array('code'=>400, 'message'=>'尚未有微信注册会员');
			return $return_data; 
		}else {
			$countNum = $result_count[0]->countNum;//总微信注册会员数
		}
		
		$result_analyse = $this->dbca->stmt_query($sql_analyse);
		
		if (empty($result_analyse)){
			$return_data = array('code'=>401, 'message'=>'尚未有注册会员发送消息');
			return $return_data;
		}
		
		$cust_arr = array(
				'mvpCust'		=> 0,
				'absoluteLive'	=> 0,
				'lightSleep'	=> 0,
				'soCust'		=> 0,
				'soLive'		=> 0,
				'soSleep'		=> 0,
				'newCust'		=> 0,
				'mayLive'		=> 0,
				'deepSleep'		=> 0
				);
		
		foreach ($result_analyse as $item){
			if ($item->lastTime >= $lastDay_less && $item->messageTimes >= $lastTimes_more){
				$cust_arr['mvpCust'] += 1;
			}
			
			if (($item->lastTime < $lastDay_less && $item->lastTime > $lastDay_more) && $item->messageTimes >= $lastTimes_more){
				$cust_arr['absoluteLive'] += 1;
			}
			
			if ($item->lastTime <= $lastDay_more && $item->messageTimes >= $lastTimes_more){
				$cust_arr['lightSleep'] += 1;
			}
			
			if ($item->lastTime >= $lastDay_less && ($item->messageTimes < $lastTimes_more && $item->messageTimes > $lastTimes_less)){
				$cust_arr['soCust'] += 1;
			}
			
			if (($item->lastTime < $lastDay_less && $item->lastTime > $lastDay_more) && ($item->messageTimes < $lastTimes_more && $item->messageTimes > $lastTimes_less)){
				$cust_arr['soLive'] += 1;
			}
			
			if ($item->lastTime <= $lastDay_more && ($item->messageTimes < $lastTimes_more && $item->messageTimes > $lastTimes_less)){
				$cust_arr['soSleep'] += 1;
			}
			
			if ($item->lastTime >= $lastDay_less && $item->messageTimes <= $lastTimes_less){
				$cust_arr['newCust'] += 1;
			}
			
			if (($item->lastTime < $lastDay_less && $item->lastTime > $lastDay_more) && $item->messageTimes <= $lastTimes_less){
				$cust_arr['mayLive'] += 1;
			}
			
			if ($item->lastTime <= $lastDay_more && $item->messageTimes <= $lastTimes_less){
				$cust_arr['deepSleep'] += 1;
			}
		}
		
		$return_data = array('code'=>200, 'data'=>array('countNum'=>$countNum, 'cust_arr'=>$cust_arr));
		
		return $return_data;
	}
	
	
	
	
	
	
	
}